package ystp.controller;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import ystp.dao.TyMapper;
import ystp.model.*;
import ystp.service.*;
import ystp.support.JsonResult;
import ystp.support.TyConfig;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("import_xiangmu")
public class ImportXiangmuController {

    private Logger logger = LoggerFactory.getLogger(getClass());

    @Value("${userfilepath}")
    private String userfilepath;

    @Resource
    KangjunyaowuService ddKangjunyaowuService;
    @Resource
    TyMapper tyMapper;

    @Resource
    TongkeyaopinService ddTongkeyaopinService;

    @Resource
    ZhuankeyaopinService ddZhuankeyaopinService;

    @Resource
    XinyaoshenqingService ddXinyaoshenqingService;

    @Resource
    YuecaikangjunyaowuService ddYuecaiangjunyaowuService;

    @Resource
    XinguotanService ddXinguotanService;

    @Resource
    ChuangxinchanpinService ddChuangxinchanpinService;

    @Resource
    ShengchanqiyelinxuanService ddShengchanqiyelinxuanService;

    @Resource
    YsrenyuanfenpeiService ddYsrenyuanfenpeiService;
    @Resource
    YsuserService ddYsuserService;

    @Autowired
    FujianService fujianService;


    @RequestMapping("import")
    public JsonResult<?> importvalue(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("xuhao") String ppxuhao,
            @RequestParam("username") String ppusername,
            @RequestParam("buzhibie") String ppbuzhibie,
            @RequestParam("account") String ppaccount,
            @RequestParam("password") String pppassword

    ){
        try {
            //用户存在
            if(ddYsuserService.isexistByAccount(ppaccount)){
                List<Ysuser> mmuser=ddYsuserService.findByAccount(ppaccount);
                String mmuserid=mmuser.get(0).getUserid();
                List<Ysrenyuanfenpei> mmyltpfenpei=ddYsrenyuanfenpeiService.findbyUserIDandXiangmuId(mmuserid,ppxianmguid);
                if(mmyltpfenpei.size()==0){
                    Ysrenyuanfenpei mmfenpei =new Ysrenyuanfenpei();
                    mmfenpei.setId(UUID.randomUUID().toString());
                    mmfenpei.setXiangmuid(ppxianmguid);
                    mmfenpei.setUserid(mmuserid);
                    mmfenpei.setIssubmit(0);
                    mmfenpei.setCreatetime(new Date());
                    return  ddYsrenyuanfenpeiService.add(mmfenpei)==true? JsonResult.getSuccessResult("导入人员分配成功")
                            : JsonResult.getErrorResult("导入人员分配失败");
                }else{
                    return JsonResult.getSuccessResult("数据重复");
                }

            }else{//用户不存在
                Ysuser mmUser=new Ysuser();
                mmUser.setUserid(UUID.randomUUID().toString());
                mmUser.setUsername(ppusername);
                mmUser.setBuzhibie(ppbuzhibie);
                mmUser.setAccount(ppaccount);
                mmUser.setPassword(pppassword);
                mmUser.setUsertype("zj");
                mmUser.setCreatetime(new Date());
                mmUser.setOrdernum(Integer.parseInt(ppxuhao));
                Ysrenyuanfenpei mmfenpei =new Ysrenyuanfenpei();
                mmfenpei.setId(UUID.randomUUID().toString());
                mmfenpei.setXiangmuid(ppxianmguid);
                mmfenpei.setUserid(mmUser.getUserid());
                mmfenpei.setIssubmit(0);
                if(ddYsuserService.add(mmUser)){
                    return  ddYsrenyuanfenpeiService.add(mmfenpei)==true? JsonResult.getSuccessResult("导入人员分配成功")
                            : JsonResult.getErrorResult("导入人员分配失败");

                }else{
                    return JsonResult.getErrorResult("导入用户失败");
                }
            }

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }
    @SuppressWarnings("resource")
    @RequestMapping("getvalue")
    public JsonResult<?> getvalue(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {
            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            //int column= sheet.getRow(0).getPhysicalNumberOfCells();
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 1; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();
                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("姓名", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("部职别", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("账号", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("密码", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> getvalue: "+e.getMessage());
            return JsonResult.getErrorResult("获取数据失败！");
        }
    }
    //8.生产企业
    @SuppressWarnings("resource")
    @RequestMapping("get_shengchanqiye")
    public JsonResult<?> get_shengchanqiye(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddShengchanqiyelinxuanService.isexistByxiangmuId(ppxianmguid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 4; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();
                if(isMergedRegion(sheet,i,0)==true){
                    mmMap.put("品种序号", getMergeRegionValue(sheet,i,0));
                }else{
                    mmMap.put("品种序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                }
                if(isMergedRegion(sheet,i,1)==true){
                    mmMap.put("药品分类", getMergeRegionValue(sheet,i,1));
                }else{
                    mmMap.put("药品分类", getCellFormatValue(sheet.getRow(i).getCell(1)));
                }
                if(isMergedRegion(sheet,i,2)==true){
                    mmMap.put("药品品名", getMergeRegionValue(sheet,i,2));
                }else{
                    mmMap.put("药品品名", getCellFormatValue(sheet.getRow(i).getCell(2)));
                }
                if(isMergedRegion(sheet,i,3)==true){
                    mmMap.put("剂型", getMergeRegionValue(sheet,i,3));
                }else{
                    mmMap.put("剂型", getCellFormatValue(sheet.getRow(i).getCell(3)));
                }
                if(isMergedRegion(sheet,i,4)==true){
                    mmMap.put("规格序号", getMergeRegionValue(sheet,i,4));
                }else{
                    mmMap.put("规格序号", getCellFormatValue(sheet.getRow(i).getCell(4)));
                }
                if(isMergedRegion(sheet,i,5)==true){
                    mmMap.put("药品名称", getMergeRegionValue(sheet,i,5));
                }else{
                    mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(5)));
                }
                if(isMergedRegion(sheet,i,6)==true){
                    mmMap.put("规格", getMergeRegionValue(sheet,i,6));
                }else{
                    mmMap.put("规格", getCellFormatValue(sheet.getRow(i).getCell(6)));
                }
                if(isMergedRegion(sheet,i,7)==true){
                    mmMap.put("单位", getMergeRegionValue(sheet,i,7));
                }else{
                    mmMap.put("单位", getCellFormatValue(sheet.getRow(i).getCell(7)));
                }

                mmMap.put("医保类别", getCellFormatValue(sheet.getRow(i).getCell(8)));
                mmMap.put("上级检查通报", getCellFormatValue(sheet.getRow(i).getCell(9)));
                mmMap.put("未挂网", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("无配送企业", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("原国谈", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("通过一致性评价", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmMap.put("国家保密配方", getCellFormatValue(sheet.getRow(i).getCell(14)));
                mmMap.put("中药保护品种", getCellFormatValue(sheet.getRow(i).getCell(15)));
                mmMap.put("价格", getCellFormatValue(sheet.getRow(i).getCell(16)));
                mmMap.put("原研", getCellFormatValue(sheet.getRow(i).getCell(17)));
                mmMap.put("基药", getCellFormatValue(sheet.getRow(i).getCell(18)));
                mmMap.put("集采", getCellFormatValue(sheet.getRow(i).getCell(19)));
                mmMap.put("院本部", getCellFormatValue(sheet.getRow(i).getCell(20)));
                mmMap.put("秦淮", getCellFormatValue(sheet.getRow(i).getCell(21)));
                mmMap.put("产品编码", getCellFormatValue(sheet.getRow(i).getCell(22)));
                mmMap.put("生产企业", getCellFormatValue(sheet.getRow(i).getCell(23)));
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(24)));
                mmMap.put("排序", getCellFormatValue(sheet.getRow(i).getCell(25)));
                mmMap.put("备注2", getCellFormatValue(sheet.getRow(i).getCell(26)));
                mmMap.put("备注3", getCellFormatValue(sheet.getRow(i).getCell(27)));
                mmMap.put("备注4", getCellFormatValue(sheet.getRow(i).getCell(28)));
                mmMap.put("备注5", getCellFormatValue(sheet.getRow(i).getCell(29)));
                mmMap.put("备注6", getCellFormatValue(sheet.getRow(i).getCell(30)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_shengchanqiye: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    @Transactional
    @RequestMapping("get_Fujian")
    public JsonResult<?> get_Fujian(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("xiangmuleixing") String ppxiangmuleixing,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(fujianService.isexistByxiangmuId(ppxianmguid,ppxiangmuleixing)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();

            int result=-1;
//            if(ppxiangmuleixing.equals("21")) {
//                List<Fujianot> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianot itme = new Fujianot();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setYouwuxuqiu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    //itme.setShifoubaogong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(15))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch21(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("22")) {
//                List<Fujianot> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianot itme = new Fujianot();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    //itme.setShifoubaogong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch21(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("23")) {
//                List<Fujiant> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujiant itme = new Fujiant();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setShenqingkeshi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setShenqingzongcishu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setShenqingzongshuliang(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(14))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch22(mmReturnList);
//            }
///*            if(ppxiangmuleixing.equals("23")) {
//                List<Fujianjsb> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianjsb itme = new Fujianjsb();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch23(mmReturnList);
//            }*/
//            if(ppxiangmuleixing.equals("24")) {
//                List<Fujiansl> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujiansl itme = new Fujiansl();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setShifouxinzengpinzhong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setShifoujicaichonghe(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setShifoujibenchonghe(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setXieyixiaoqi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch24(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("25")) {
//                List<Fujianw> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianw itme = new Fujianw();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setShifouxinzengpinzhong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setShifoujibenxiangtong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setXieyixiaoqi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch25(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("26")) {
//                List<Fujianq> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianq itme = new Fujianq();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYouwulinchuangxuqiu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setShenqingkeshi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch26(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("27")||ppxiangmuleixing.equals("35")) {
//                List<Fujianb> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianb itme = new Fujianb();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setShifouxinzengpinzhong(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setYouwulinchuangxuqiu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setShenqingkeshi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setYouwutonggongxiao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setYuxuanhuiyijian(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(14))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(15))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch27(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("28")) {
//                List<Fujianjs> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianjs itme = new Fujianjs();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setCaigourenwu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setXieyixiaoqi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch28(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("29")) {
//                List<Fujianjs> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianjs itme = new Fujianjs();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setCaigourenwu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch28(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("30")) {
//                List<Fujiantihuan> mmReturnList = new ArrayList<>();
//                for (int i = 3; i < rows; i++) { //行
//                    Fujiantihuan itme = new Fujiantihuan();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setXyguige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setXydanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setXyjixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setXyshengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setXyjiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setXyyibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setBeizhu2(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setTzguige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setTzdanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setTzjixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setTzshengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(14))));
//                    itme.setTzjiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(15))));
//                    itme.setTzyibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(16))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(17))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch30(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("31")) {
//                List<Fujianyuanyan> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianyuanyan itme = new Fujianyuanyan();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setYuanyan(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch31(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("32")) {
//                List<Fujianshiliu> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujianshiliu itme = new Fujianshiliu();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setChanpinbianma(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJixing(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setYibao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setShifouzhiliaoyongyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setYuxuanhuiyijian(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch32(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("33")) {
//                List<Fujiankjjiben> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujiankjjiben itme = new Fujiankjjiben();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setFenlei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setBeizhu2(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setBeizhu3(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setBeizhu4(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYibaoleibie(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setZhidaoyuanze(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setLincaizongcishu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(14))));
//                    itme.setLincaizongshulinag(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(15))));
//                    itme.setShenqingkeshi(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(16))));
//                    itme.setBeizhu5(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(17))));
//                    itme.setYuxuanhuiyijian(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(18))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(19))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch33(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("34")) {
//                List<Fujiankjyuecai> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujiankjyuecai itme = new Fujiankjyuecai();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setFenlei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setBeizhu2(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setShengchanqiye(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));
//                    itme.setYibaoleibie(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));
//                    itme.setBeizhu3(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setYuxuanhuiyijian(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch34(mmReturnList);
//            }
//            if(ppxiangmuleixing.equals("36")) {
//                List<Fujiankjjiben> mmReturnList = new ArrayList<>();
//                for (int i = 2; i < rows; i++) { //行
//                    Fujiankjjiben itme = new Fujiankjjiben();
//                    itme.setId(UUID.randomUUID().toString());
//                    itme.setXiangmuid(ppxianmguid);
//                    itme.setXuhao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(0))));
//                    itme.setBeuzhu1(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(1))));//产品编码
//                    itme.setYaopinmingcheng(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(2))));
//                    itme.setFenlei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(3))));
//                    itme.setGuige(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(4))));
//                    itme.setJiage(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(5))));
//                    itme.setDanwei(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(6))));
//                    itme.setBeizhu3(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(7))));//剂型
//                    itme.setBeizhu4(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(8))));//生产企业
//                    itme.setYibaoleibie(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(9))));
//                    itme.setJiyao(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(10))));
//                    itme.setYizhixingpingjia(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(11))));
//                    itme.setZhidaoyuanze(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(12))));
//                    itme.setYuxuanhuiyijian(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(13))));
//                    itme.setBeizhu(String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(14))));
//                    itme.setOrdernumber(i - 1);
//                    itme.setCreatetime(new Date());
//                    mmReturnList.add(itme);
//                }
//                result =  fujianService.insertBatch33(mmReturnList);
//            }
            {
                List<Ty> mmReturnList = new ArrayList<>();
                for (int i = 2; i < rows; i++) { //行
                    Ty itme = new Ty();
                    itme.setId(UUID.randomUUID().toString());
                    itme.setXiangmuid(ppxianmguid);
                    for(int j=0;j< TyConfig.getExel(ppxiangmuleixing);j++){
                        itme.setAttribute("text"+(j+1),String.valueOf(getCellFormatValue(sheet.getRow(i).getCell(j))));
                    }
                    itme.setIsvote(1);
                    itme.setOrdernumber(i - 1);
                    itme.setCreatetime(new Date());
                    mmReturnList.add(itme);
                }
                result =  tyMapper.insertBatch(mmReturnList);
            }

            in.close();

            if(result==0){
                return JsonResult.getErrorResult("插入失败！");
            }
            return JsonResult.getSuccessResult("");


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_Fujian: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    @RequestMapping("get_kangjunyaowu")
    public JsonResult<?> get_kangjunyaowu(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddKangjunyaowuService.isexistByxiangmuId(ppxianmguid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 4; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();
                if(isMergedRegion(sheet,i,0)==true){
                    mmMap.put("品种", getMergeRegionValue(sheet,i,0));
                }else{
                    mmMap.put("品种", getCellFormatValue(sheet.getRow(i).getCell(0)));
                }
                mmMap.put("品规", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("分类", getCellFormatValue(sheet.getRow(i).getCell(3)));
                if(isMergedRegion(sheet,i,4)==true){
                    mmMap.put("品种品规限制", getMergeRegionValue(sheet,i,4));
                }else{
                    mmMap.put("品种品规限制", getCellFormatValue(sheet.getRow(i).getCell(4)));
                }
                if(isMergedRegion(sheet,i,5)==true){
                    mmMap.put("合并行", getMergeRegionValue(sheet,i,5));
                }else{
                    mmMap.put("合并行", getCellFormatValue(sheet.getRow(i).getCell(5)));
                }
                if(isMergedRegion(sheet,i,6)==true){
                    mmMap.put("限制数", getMergeRegionValue(sheet,i,6));
                }else{
                    mmMap.put("限制数", getCellFormatValue(sheet.getRow(i).getCell(6)));
                }
                if(isMergedRegion(sheet,i,7)==true){
                    mmMap.put("规则编号", getMergeRegionValue(sheet,i,7));
                }else{
                    mmMap.put("规则编号", getCellFormatValue(sheet.getRow(i).getCell(7)));
                }

                mmMap.put("规则描述", getCellFormatValue(sheet.getRow(i).getCell(8)));
                mmMap.put("规格", getCellFormatValue(sheet.getRow(i).getCell(9)));
                mmMap.put("价格", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("单位", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("医保类别", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("国家基本药物", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmMap.put("集采药", getCellFormatValue(sheet.getRow(i).getCell(14)));
                mmMap.put("通过评价", getCellFormatValue(sheet.getRow(i).getCell(15)));
                mmMap.put("指导原则", getCellFormatValue(sheet.getRow(i).getCell(16)));
                mmMap.put("院本部药", getCellFormatValue(sheet.getRow(i).getCell(17)));
                mmMap.put("秦淮药", getCellFormatValue(sheet.getRow(i).getCell(18)));

                //mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(19)));
                if(getCellFormatValue(sheet.getRow(i).getCell(19))!=null&&getCellFormatValue(sheet.getRow(i).getCell(19)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("东总记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(19)).toString()));
                }else {
                    mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(19)));
                }

                if(getCellFormatValue(sheet.getRow(i).getCell(20))!=null&&getCellFormatValue(sheet.getRow(i).getCell(20)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("秦淮记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(20)).toString()));
                }else {
                    mmMap.put("秦淮记录", getCellFormatValue(sheet.getRow(i).getCell(20)));
                }

                if(isMergedRegion(sheet,i,21)==true){
                    mmMap.put("指导意见", getMergeRegionValue(sheet,i,21));
                }else{
                    mmMap.put("指导意见", getCellFormatValue(sheet.getRow(i).getCell(21)));
                }
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(22)));
                mmMap.put("品种排名", getCellFormatValue(sheet.getRow(i).getCell(23)));
                mmMap.put("品规得票", getCellFormatValue(sheet.getRow(i).getCell(24)));
                mmMap.put("子规则限制数", getCellFormatValue(sheet.getRow(i).getCell(25)));
                mmMap.put("子规则编号", getCellFormatValue(sheet.getRow(i).getCell(26)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_tiwaizhenduan: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    public static String ExcelDoubleToDate(String strDate) {
        if(strDate.length() == 5){
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = DoubleToDate(Double.parseDouble(strDate));
                return sdf.format(tDate);
            }catch (Exception e){
                e.printStackTrace();
                return strDate;
            }
        }
        return strDate;
    }

    public static Date DoubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));

        return tDate;
    }

    //2.通科
    @SuppressWarnings("resource")
    @RequestMapping("get_tongkeyaopin")
    public JsonResult<?> get_tongkeyaopin(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddTongkeyaopinService.isexistByxiangmuId(ppxianmguid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 4; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("规格", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("生产厂家", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("单位", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("价格", getCellFormatValue(sheet.getRow(i).getCell(5)));
                mmMap.put("医保类别", getCellFormatValue(sheet.getRow(i).getCell(6)));
                mmMap.put("基药", getCellFormatValue(sheet.getRow(i).getCell(7)));
                mmMap.put("集采药", getCellFormatValue(sheet.getRow(i).getCell(8)));
                mmMap.put("通过评价", getCellFormatValue(sheet.getRow(i).getCell(9)));
                mmMap.put("中药保密配方", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("国家中药保护", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("原国谈药", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("创新药", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmMap.put("重点监控药", getCellFormatValue(sheet.getRow(i).getCell(14)));
                mmMap.put("中药注射剂", getCellFormatValue(sheet.getRow(i).getCell(15)));
                mmMap.put("严重不良反应", getCellFormatValue(sheet.getRow(i).getCell(16)));
                mmMap.put("掉网药", getCellFormatValue(sheet.getRow(i).getCell(17)));
                mmMap.put("院本部药", getCellFormatValue(sheet.getRow(i).getCell(18)));
                mmMap.put("秦淮药", getCellFormatValue(sheet.getRow(i).getCell(19)));
                //mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(19)));
                if(getCellFormatValue(sheet.getRow(i).getCell(20))!=null&&getCellFormatValue(sheet.getRow(i).getCell(20)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("东总记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(20)).toString()));
                }else {
                    mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(20)));
                }

                if(getCellFormatValue(sheet.getRow(i).getCell(21))!=null&&getCellFormatValue(sheet.getRow(i).getCell(21)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("秦淮记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(21)).toString()));
                }else {
                    mmMap.put("秦淮记录", getCellFormatValue(sheet.getRow(i).getCell(21)));
                }

                mmMap.put("专科分组", getCellFormatValue(sheet.getRow(i).getCell(22)));
                mmMap.put("专科组预选意见", getCellFormatValue(sheet.getRow(i).getCell(23)));
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(24)));
                if(isMergedRegion(sheet,i,25)==true){
                    mmMap.put("限制数", getMergeRegionValue(sheet,i,25));
                }else{
                    mmMap.put("限制数", getCellFormatValue(sheet.getRow(i).getCell(25)));
                }
                if(isMergedRegion(sheet,i,26)==true){
                    mmMap.put("规则编号", getMergeRegionValue(sheet,i,26));
                }else{
                    mmMap.put("规则编号", getCellFormatValue(sheet.getRow(i).getCell(26)));
                }

                mmMap.put("规则描述", getCellFormatValue(sheet.getRow(i).getCell(27)));
                mmMap.put("淘汰", getCellFormatValue(sheet.getRow(i).getCell(28)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_tiwaizhenduan: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    //3.专科
    @SuppressWarnings("resource")
    @RequestMapping("get_zhuankeyaopin")
    public JsonResult<?> get_zhuankeyaopin(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddTongkeyaopinService.isexistByxiangmuId(ppxianmguid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 4; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("规格", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("生产厂家", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("单位", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("价格", getCellFormatValue(sheet.getRow(i).getCell(5)));
                mmMap.put("医保类别", getCellFormatValue(sheet.getRow(i).getCell(6)));
                mmMap.put("基药", getCellFormatValue(sheet.getRow(i).getCell(7)));
                mmMap.put("集采药", getCellFormatValue(sheet.getRow(i).getCell(8)));
                mmMap.put("通过评价", getCellFormatValue(sheet.getRow(i).getCell(9)));
                mmMap.put("中药保密配方", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("国家中药保护", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("原国谈药", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("创新药", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmMap.put("重点监控药", getCellFormatValue(sheet.getRow(i).getCell(14)));
                mmMap.put("中药注射剂", getCellFormatValue(sheet.getRow(i).getCell(15)));
                mmMap.put("严重不良反应", getCellFormatValue(sheet.getRow(i).getCell(16)));
                mmMap.put("掉网药", getCellFormatValue(sheet.getRow(i).getCell(17)));
                mmMap.put("院本部药", getCellFormatValue(sheet.getRow(i).getCell(18)));
                mmMap.put("秦淮药", getCellFormatValue(sheet.getRow(i).getCell(19)));
                //mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(19)));
                if(getCellFormatValue(sheet.getRow(i).getCell(20))!=null&&getCellFormatValue(sheet.getRow(i).getCell(20)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("东总记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(20)).toString()));
                }else {
                    mmMap.put("东总记录", getCellFormatValue(sheet.getRow(i).getCell(20)));
                }

                if(getCellFormatValue(sheet.getRow(i).getCell(21))!=null&&getCellFormatValue(sheet.getRow(i).getCell(21)).toString()!=""){
                    //Object mm=getCellFormatValue(sheet.getRow(i).getCell(20));
                    mmMap.put("秦淮记录", ExcelDoubleToDate(getCellFormatValue(sheet.getRow(i).getCell(21)).toString()));
                }else {
                    mmMap.put("秦淮记录", getCellFormatValue(sheet.getRow(i).getCell(21)));
                }

                mmMap.put("专科分组", getCellFormatValue(sheet.getRow(i).getCell(22)));
                mmMap.put("专科组预选意见", getCellFormatValue(sheet.getRow(i).getCell(23)));
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(24)));
                if(isMergedRegion(sheet,i,25)==true){
                    mmMap.put("限制数", getMergeRegionValue(sheet,i,25));
                }else{
                    mmMap.put("限制数", getCellFormatValue(sheet.getRow(i).getCell(25)));
                }
                if(isMergedRegion(sheet,i,26)==true){
                    mmMap.put("规则编号", getMergeRegionValue(sheet,i,26));
                }else{
                    mmMap.put("规则编号", getCellFormatValue(sheet.getRow(i).getCell(26)));
                }
                mmMap.put("规则描述", getCellFormatValue(sheet.getRow(i).getCell(27)));

                mmMap.put("必淘汰", getCellFormatValue(sheet.getRow(i).getCell(28)));
                mmMap.put("预选会意见", getCellFormatValue(sheet.getRow(i).getCell(29)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_tiwaizhenduan: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    //新国谈
    @SuppressWarnings("resource")
    @RequestMapping("get_xinguotan")
    public JsonResult<?> get_xinguotan(
            @RequestParam("xiangmuid") String ppxiangmuid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddXinguotanService.isExistByXiangmuId(ppxiangmuid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 3; i < rows ; i++) { //行

                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("医保", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("院本部药", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("秦淮药", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("专科分组", getCellFormatValue(sheet.getRow(i).getCell(5)));
                mmMap.put("申请科室", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("备注2", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("备注3", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_xinguotan: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    //创新
    @SuppressWarnings("resource")
    //创新药品get
    @RequestMapping("get_chuangxinchanpin")
    public JsonResult<?> get_chuangxinchanpin(
            @RequestParam("xiangmuid") String ppxiangmuid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddChuangxinchanpinService.isExistByXiangmuId(ppxiangmuid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 4; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("规格", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("单位", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("价格", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("生产厂家", getCellFormatValue(sheet.getRow(i).getCell(5)));
                mmMap.put("执行时间", getCellFormatValue(sheet.getRow(i).getCell(6)));
                mmMap.put("院本部", getCellFormatValue(sheet.getRow(i).getCell(7)));
                mmMap.put("秦淮", getCellFormatValue(sheet.getRow(i).getCell(8)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_chuangxinchanpin: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    //新药
    @RequestMapping("get_xinyaoshenqing")
    public JsonResult<?> get_xinyaoshenqing(
            @RequestParam("xiangmuid") String ppxiangmuid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddXinyaoshenqingService.isExistByXiangmuId(ppxiangmuid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 2; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("规格包装", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("申请科室", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("专科组别", getCellFormatValue(sheet.getRow(i).getCell(5)));
                mmMap.put("是否是基药", getCellFormatValue(sheet.getRow(i).getCell(6)));
                mmMap.put("是否是创新药", getCellFormatValue(sheet.getRow(i).getCell(9)));
                mmMap.put("是否是指南推荐", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("是否有临床基础", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("临采5次以上年份", getCellFormatValue(sheet.getRow(i).getCell(17)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_xinyaoshenqing: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    //约采
    @SuppressWarnings("resource")
    @RequestMapping("get_yuecaikangjunyaowu")
    public JsonResult<?> get_yuecaikangjunyaowu(
            @RequestParam("xiangmuid") String ppxiangmuid,
            @RequestParam("fileurl") String mmFileUrl,
            HttpServletRequest request){
        try {

            if(ddYuecaiangjunyaowuService.isExistByXiangmuId(ppxiangmuid)){
                return JsonResult.getErrorResult("导入失败！该项目已存在数据，请删除后再导入。");
            }

            //取出文档存放的根目录，并去除文件最后的斜杠
            if(userfilepath.endsWith("/")){
                userfilepath  = userfilepath.substring(0, userfilepath.length()-1);
            }

            mmFileUrl=mmFileUrl.substring(mmFileUrl.indexOf("importexecl"));
            String url = userfilepath+"/"+mmFileUrl;
            Workbook book = null;

            File f = new File(url);
            InputStream in = new FileInputStream(f);

            String prefix = url.substring(url.lastIndexOf(".")+1);
            if(prefix.equals("xls")) {
                book = new HSSFWorkbook(in);
            }else if(prefix.equals("xlsx")) {
                book = new XSSFWorkbook(in);
            }else {
                return JsonResult.getErrorResult("请上传xls或xlsx类型的文件！");
            }

            Sheet sheet = book.getSheetAt(0);
            if (sheet==null){
                return JsonResult.getErrorResult("请上传规范格式的EXCEL（确保上传数据在SHEET1中）！");
            }
            int rows = sheet.getPhysicalNumberOfRows();
            List<Map<String,Object>> mmReturnList = new ArrayList<Map<String,Object>>();

            for (int i = 3; i < rows ; i++) { //行
                Map<String,Object> mmMap = new HashMap<String,Object>();

                mmMap.put("序号", getCellFormatValue(sheet.getRow(i).getCell(0)));
                mmMap.put("药品名称", getCellFormatValue(sheet.getRow(i).getCell(1)));
                mmMap.put("医保", getCellFormatValue(sheet.getRow(i).getCell(2)));
                mmMap.put("院本部药", getCellFormatValue(sheet.getRow(i).getCell(3)));
                mmMap.put("秦淮药", getCellFormatValue(sheet.getRow(i).getCell(4)));
                mmMap.put("药品类别", getCellFormatValue(sheet.getRow(i).getCell(5)));
               /* mmMap.put("申请科室", getCellFormatValue(sheet.getRow(i).getCell(11)));*/
                mmMap.put("备注4", getCellFormatValue(sheet.getRow(i).getCell(10)));
                mmMap.put("备注", getCellFormatValue(sheet.getRow(i).getCell(11)));
                mmMap.put("备注2", getCellFormatValue(sheet.getRow(i).getCell(12)));
                mmMap.put("备注3", getCellFormatValue(sheet.getRow(i).getCell(13)));
                mmReturnList.add(mmMap);
            }

            in.close();
            return JsonResult.getSuccessResult(mmReturnList);


        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> get_yuecaikangjunyaowu: "+e.getMessage());
            return JsonResult.getErrorResult("获取项目中的数据失败！");
        }
    }

    public static boolean isMergedRegion(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <=lastRow){
                if (column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }
    public static String getMergeRegionValue(Sheet sheet,int row,int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <=lastRow){
                if (column >= firstColumn && column <= lastColumn){
                    Row frow = sheet.getRow(firstRow);
                    Cell cell = frow.getCell(firstColumn);
                    return getCellValue(cell);
                }
            }
        }
        return null;
    }
    public static String getCellValue(Cell cell){

        if (cell == null) return "";
        return cell.toString();
    }
    //1.抗菌
    @RequestMapping("import_kangjun")
    public JsonResult<?> importKangjunvalue(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("pingui") String ppPingui,
            @RequestParam("pinzhong") String ppPinzhong,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("xianzhitiaojian") String ppXianzhitiaojian,
            @RequestParam("hebinghang") String ppHebinghang,
            @RequestParam("xianzhishu") String ppXianzhishu,
            @RequestParam("guizebianhao") String ppGuizebianhao,
            @RequestParam("guizeshuoming") String ppGuizeshuoming,
            @RequestParam("fenlei") String ppFenlei,
            @RequestParam("guige") String ppGuige,
            @RequestParam("danwei") String ppDanwei,
            @RequestParam("jiage") String ppJiage,
            @RequestParam("yibaoleibie") String ppYibaoleibie,
            @RequestParam("guojiajibenyaowu") String ppGuojiajibenyaowu,
            @RequestParam("jicaiyao") String ppJicaiyao,
            @RequestParam("tongguopingjia") String ppTongguopingjia,
            @RequestParam("zhidaoyuanze") String ppZhidaoyuanze,
            @RequestParam("dongzong") String ppDongzong,
            @RequestParam("dongzongjilu") String ppDongzongjilu,
            @RequestParam("qinhuaiyao") String ppQinhuaiyao,
            @RequestParam("qinhuaijilu") String ppQinhuaijilu,
            @RequestParam("pinzhongpaiming") String ppPinzhongpaiming,
            @RequestParam("pinguipiaoshu") String ppPinguipiaoshu,
            @RequestParam("zhidaoyijian") String ppZhidaoyijian,
            @RequestParam("beizhu2") String ppBeizhu2,
            @RequestParam("beizhu3") String ppBeizhu3,
            @RequestParam("beizhu") String ppBeizhu


    ){
        try {
            Kangjunyaowu mmKangjunyaowu=new Kangjunyaowu();
            mmKangjunyaowu.setId(UUID.randomUUID().toString());
            mmKangjunyaowu.setPingui(ppPingui);
            mmKangjunyaowu.setXiangmuid(ppxianmguid);
            Double f = Double.valueOf(ppPinzhong);
            int a = (int)Math.ceil(f);
            mmKangjunyaowu.setPinzhong(a);
            mmKangjunyaowu.setYaopinmingcheng(ppYaopinmingcheng);
            mmKangjunyaowu.setXianzhitiaojian(ppXianzhitiaojian);
            mmKangjunyaowu.setHebinghang(ppHebinghang);
            mmKangjunyaowu.setXianzhishu(ppXianzhishu);
            mmKangjunyaowu.setGuizeshuoming(ppGuizeshuoming);
            mmKangjunyaowu.setGuizebianhao(ppGuizebianhao);
            mmKangjunyaowu.setFenlei(ppFenlei);
            mmKangjunyaowu.setGuige(ppGuige);
            mmKangjunyaowu.setDanwei(ppDanwei);
            mmKangjunyaowu.setJiage(ppJiage);
            mmKangjunyaowu.setYibaoleibie(ppYibaoleibie);
            mmKangjunyaowu.setGuojiajibenyaowu(ppGuojiajibenyaowu);
            mmKangjunyaowu.setJicaiyao(ppJicaiyao);
            mmKangjunyaowu.setTongguopingjia(ppTongguopingjia);
            mmKangjunyaowu.setZhidaoyuanze(ppZhidaoyuanze);
            mmKangjunyaowu.setDongzongyao(ppDongzong);
            SimpleDateFormat simdate=new SimpleDateFormat("yyyy-MM-dd");
            if("".equals(ppDongzongjilu)){
                mmKangjunyaowu.setDongzongjilu(null);
            }else {
                Date mmDongzongjilu=simdate.parse(ppDongzongjilu);
                mmKangjunyaowu.setDongzongjilu(mmDongzongjilu);
            }
            if("".equals(ppQinhuaijilu)){
                mmKangjunyaowu.setQinhuaijilu(null);
            }else {
                Date mmQinhuaijilu=simdate.parse(ppQinhuaijilu);
                mmKangjunyaowu.setQinhuaijilu(mmQinhuaijilu);
            }
            mmKangjunyaowu.setQinhuaiyao(ppQinhuaiyao);

            mmKangjunyaowu.setZhidaoyijian(ppZhidaoyijian);
            mmKangjunyaowu.setBeizhu(ppBeizhu);
            mmKangjunyaowu.setPinzhongpaiming(ppPinzhongpaiming);
            mmKangjunyaowu.setPinguipiaoshu(ppPinguipiaoshu);
            mmKangjunyaowu.setOrdernumber(Integer.parseInt(ppPingui));
            mmKangjunyaowu.setCreatetime(new Date());
            mmKangjunyaowu.setBeizhu2(ppBeizhu2);
            mmKangjunyaowu.setBeizhu3(ppBeizhu3);


            return ddKangjunyaowuService.add(mmKangjunyaowu)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_kangjun: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //2.通科
    @RequestMapping("import_tongkeyaopin")
    public JsonResult<?> importTongkeyaopin(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("xuhao") String ppXuhao,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("shengchanchangjia") String ppShengchanchangjia,
            @RequestParam("guige") String ppGuige,
            @RequestParam("danwei") String ppDanwei,
            @RequestParam("jiage") String ppJiage,
            @RequestParam("yibaoleibie") String ppYibaoleibie,
            @RequestParam("guojiajibenyaowu") String ppGuojiajibenyaowu,
            @RequestParam("jicaiyao") String ppJicaiyao,
            @RequestParam("tongguopingjia") String ppTongguopingjia,
            @RequestParam("baomipeifang") String ppBaomipeifang,
            @RequestParam("zhongyaobaohu") String ppZhongyaobaohu,
            @RequestParam("yuanguotanyao") String ppYuanguotanyao,
            @RequestParam("chuanxinyao") String ppChuanxinyao,
            @RequestParam("jiankongyao") String ppJiankongyao,
            @RequestParam("zhusheji") String ppZhusheji,
            @RequestParam("buliangfanying") String ppBuliangfanying,
            @RequestParam("diaowangyao") String ppDiaowangyao,
            @RequestParam("dongzong") String ppDongzong,
            @RequestParam("dongzongjilu") String ppDongzongjilu,
            @RequestParam("qinhuaiyao") String ppQinhuaiyao,
            @RequestParam("qinhuaijilu") String ppQinhuaijilu,
            @RequestParam("zhuankefenzu") String ppZhuankefenzu,
            @RequestParam("yuxuanyijian") String ppYuxuanyijian,
            @RequestParam("beizhu") String ppBeizhu,
            @RequestParam("xianzhishu") String ppXianzhishu,
            @RequestParam("guizebianhao") String ppGuizebianhao,
            @RequestParam("guizeshuoming") String ppGuizeshuoming,
            @RequestParam("taotai") String ppTaotai

    ){
        try {
            Tongkeyaopin mmTongkeyaopin=new Tongkeyaopin();
            mmTongkeyaopin.setId(UUID.randomUUID().toString());
            mmTongkeyaopin.setXuhao(ppXuhao);
            mmTongkeyaopin.setXiangmuid(ppxianmguid);
            mmTongkeyaopin.setYaopinmingcheng(ppYaopinmingcheng);
            mmTongkeyaopin.setShengchanchangjia(ppShengchanchangjia);
            mmTongkeyaopin.setGuige(ppGuige);
            mmTongkeyaopin.setDanwei(ppDanwei);
            mmTongkeyaopin.setJiage(ppJiage);
            mmTongkeyaopin.setYibaoleibie(ppYibaoleibie);
            mmTongkeyaopin.setGuojiajibenyaowu(ppGuojiajibenyaowu);
            mmTongkeyaopin.setJicaiyao(ppJicaiyao);
            mmTongkeyaopin.setTongguopingjia(ppTongguopingjia);
            mmTongkeyaopin.setBaomipeifang(ppBaomipeifang);
            mmTongkeyaopin.setZhongyaobaohu(ppZhongyaobaohu);
            mmTongkeyaopin.setYuanguotanyao(ppYuanguotanyao);
            mmTongkeyaopin.setChuanxinyao(ppChuanxinyao);
            mmTongkeyaopin.setJiankongyao(ppJiankongyao);
            mmTongkeyaopin.setZhusheji(ppZhusheji);
            mmTongkeyaopin.setBuliangfanying(ppBuliangfanying);
            mmTongkeyaopin.setDiaowangyao(ppDiaowangyao);
            mmTongkeyaopin.setYuxuanyijian(ppYuxuanyijian);
            mmTongkeyaopin.setZhuankefenzu(ppZhuankefenzu);
            mmTongkeyaopin.setBeizhu(ppBeizhu);
            mmTongkeyaopin.setXianzhishu(ppXianzhishu);
            mmTongkeyaopin.setGuizeshuoming(ppGuizeshuoming);
            mmTongkeyaopin.setGuizebianhao(ppGuizebianhao);
            mmTongkeyaopin.setTaotai(ppTaotai);
            mmTongkeyaopin.setDongzongyao(ppDongzong);
            SimpleDateFormat simdate=new SimpleDateFormat("yyyy-MM-dd");
            if("".equals(ppDongzongjilu)){
                mmTongkeyaopin.setDongzongjilu(null);
            }else {
                Date mmDongzongjilu=simdate.parse(ppDongzongjilu);
                mmTongkeyaopin.setDongzongjilu(mmDongzongjilu);
            }
            if("".equals(ppQinhuaijilu)){
                mmTongkeyaopin.setQinhuaijilu(null);
            }else {
                Date mmQinhuaijilu=simdate.parse(ppQinhuaijilu);
                mmTongkeyaopin.setQinhuaijilu(mmQinhuaijilu);
            }
            mmTongkeyaopin.setQinhuaiyao(ppQinhuaiyao);

            mmTongkeyaopin.setOrdernumber(Integer.parseInt(ppXuhao));
            mmTongkeyaopin.setCreatetime(new Date());


            return ddTongkeyaopinService.add(mmTongkeyaopin)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_kangjun: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //3.专科
    @RequestMapping("import_zhuankeyaopin")
    public JsonResult<?> importZhuankeyaopin(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("xuhao") String ppXuhao,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("shengchanchangjia") String ppShengchanchangjia,
            @RequestParam("guige") String ppGuige,
            @RequestParam("danwei") String ppDanwei,
            @RequestParam("jiage") String ppJiage,
            @RequestParam("yibaoleibie") String ppYibaoleibie,
            @RequestParam("guojiajibenyaowu") String ppGuojiajibenyaowu,
            @RequestParam("jicaiyao") String ppJicaiyao,
            @RequestParam("tongguopingjia") String ppTongguopingjia,
            @RequestParam("baomipeifang") String ppBaomipeifang,
            @RequestParam("zhongyaobaohu") String ppZhongyaobaohu,
            @RequestParam("yuanguotanyao") String ppYuanguotanyao,
            @RequestParam("chuanxinyao") String ppChuanxinyao,
            @RequestParam("jiankongyao") String ppJiankongyao,
            @RequestParam("zhusheji") String ppZhusheji,
            @RequestParam("buliangfanying") String ppBuliangfanying,
            @RequestParam("diaowangyao") String ppDiaowangyao,
            @RequestParam("dongzong") String ppDongzong,
            @RequestParam("dongzongjilu") String ppDongzongjilu,
            @RequestParam("qinhuaiyao") String ppQinhuaiyao,
            @RequestParam("qinhuaijilu") String ppQinhuaijilu,
            @RequestParam("zhuankefenzu") String ppZhuankefenzu,
            @RequestParam("yuxuanyijian") String ppYuxuanyijian,
            @RequestParam("beizhu") String ppBeizhu,
            @RequestParam("xianzhishu") String ppXianzhishu,
            @RequestParam("guizebianhao") String ppGuizebianhao,
            @RequestParam("guizeshuoming") String ppGuizeshuoming,
            @RequestParam("taotai") String ppTaotai,
            @RequestParam("beizhu2") String ppBeizhu2

    ){
        try {
            Zhuankeyaopin mmZhuankeyaopin=new Zhuankeyaopin();
            mmZhuankeyaopin.setId(UUID.randomUUID().toString());
            mmZhuankeyaopin.setXuhao(ppXuhao);
            mmZhuankeyaopin.setXiangmuid(ppxianmguid);
            mmZhuankeyaopin.setYaopinmingcheng(ppYaopinmingcheng);
            mmZhuankeyaopin.setShengchanchangjia(ppShengchanchangjia);
            mmZhuankeyaopin.setGuige(ppGuige);
            mmZhuankeyaopin.setDanwei(ppDanwei);
            mmZhuankeyaopin.setJiage(ppJiage);
            mmZhuankeyaopin.setYibaoleibie(ppYibaoleibie);
            mmZhuankeyaopin.setGuojiajibenyaowu(ppGuojiajibenyaowu);
            mmZhuankeyaopin.setJicaiyao(ppJicaiyao);
            mmZhuankeyaopin.setTongguopingjia(ppTongguopingjia);
            mmZhuankeyaopin.setBaomipeifang(ppBaomipeifang);
            mmZhuankeyaopin.setZhongyaobaohu(ppZhongyaobaohu);
            mmZhuankeyaopin.setYuanguotanyao(ppYuanguotanyao);
            mmZhuankeyaopin.setChuanxinyao(ppChuanxinyao);
            mmZhuankeyaopin.setJiankongyao(ppJiankongyao);
            mmZhuankeyaopin.setZhusheji(ppZhusheji);
            mmZhuankeyaopin.setBuliangfanying(ppBuliangfanying);
            mmZhuankeyaopin.setDiaowangyao(ppDiaowangyao);
            mmZhuankeyaopin.setYuxuanyijian(ppYuxuanyijian);
            mmZhuankeyaopin.setZhuankefenzu(ppZhuankefenzu);
            mmZhuankeyaopin.setBeizhu(ppBeizhu);
            mmZhuankeyaopin.setXianzhishu(ppXianzhishu);
            mmZhuankeyaopin.setGuizeshuoming(ppGuizeshuoming);
            mmZhuankeyaopin.setGuizebianhao(ppGuizebianhao);
            mmZhuankeyaopin.setTaotai(ppTaotai);
            mmZhuankeyaopin.setBeizhu2(ppBeizhu2);
            mmZhuankeyaopin.setDongzongyao(ppDongzong);
            SimpleDateFormat simdate=new SimpleDateFormat("yyyy-MM-dd");
            if("".equals(ppDongzongjilu)){
                mmZhuankeyaopin.setDongzongjilu(null);
            }else {
                Date mmDongzongjilu=simdate.parse(ppDongzongjilu);
                mmZhuankeyaopin.setDongzongjilu(mmDongzongjilu);
            }
            if("".equals(ppQinhuaijilu)){
                mmZhuankeyaopin.setQinhuaijilu(null);
            }else {
                Date mmQinhuaijilu=simdate.parse(ppQinhuaijilu);
                mmZhuankeyaopin.setQinhuaijilu(mmQinhuaijilu);
            }
            mmZhuankeyaopin.setQinhuaiyao(ppQinhuaiyao);

            mmZhuankeyaopin.setOrdernumber(Integer.parseInt(ppXuhao));
            mmZhuankeyaopin.setCreatetime(new Date());


            return ddZhuankeyaopinService.add(mmZhuankeyaopin)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_kangjun: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //4.新国谈
    @RequestMapping("import_xinguotan")
    public JsonResult<?> importxinguotanvalue(
            @RequestParam("xiangmuid") String ppXiangmuId,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("yibao") String ppYibao,
           /* @RequestParam("guize") String ppGuize,*/
            @RequestParam("yuanbenbuyao") String ppYuanbenbuyao,
            @RequestParam("qinhuaiyao") String ppQinhuaiyao,
            @RequestParam("zhuankefenzu") String ppZhuankefenzu,
            @RequestParam("shenqingkeshi") String ppShenqingkeshi,
            @RequestParam("beizhu") String ppBeizhu,
            @RequestParam("beizhu2") String ppBeizhu2,
            @RequestParam("beizhu3") String ppBeizhu3,
            @RequestParam("xuhao") String ppXuhao

    ){
        try {
            Xinguotan mmXinguotan=new Xinguotan();
            mmXinguotan.setId(UUID.randomUUID().toString());
            mmXinguotan.setXiangmuid(ppXiangmuId);
            mmXinguotan.setYaopinmingcheng(ppYaopinmingcheng);
            mmXinguotan.setYibao(ppYibao);
           /* mmXinguotan.setGuize(ppGuize);*/
            mmXinguotan.setYuanbenbuyao(ppYuanbenbuyao);
            mmXinguotan.setQinhuaiyao(ppQinhuaiyao);
            mmXinguotan.setZhuankefenzu(ppZhuankefenzu);
            mmXinguotan.setShenqingkeshi(ppShenqingkeshi);
            mmXinguotan.setBeizhu(ppBeizhu);
            mmXinguotan.setBeizhu2(ppBeizhu2);
            mmXinguotan.setBeizhu3(ppBeizhu3);
            mmXinguotan.setOrdernumber(Integer.parseInt(ppXuhao));
            mmXinguotan.setCreatetime(new Date());


            return ddXinguotanService.add(mmXinguotan)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_xinguotan: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //5.创新产品导入
    @RequestMapping("import_chuangxinchanpin")
    public JsonResult<?> importchuangxinchanpinvalue(
            @RequestParam("xiangmuid") String ppXiangmuId,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("guige") String ppGuige,
            @RequestParam("danwei") String ppDanwei,
            @RequestParam("jiage") String ppJiage,
            @RequestParam("shengchanchangjia") String ppShengchanchangjia,
            @RequestParam("zhixingshijian") String ppZhixingshijian,
            @RequestParam("yuanbenbu") String ppYuanbenbu,
            @RequestParam("qinhuai") String ppQinhuai,
            @RequestParam("xuhao") String ppXuhao

    ){
        try {
            Chuangxinchanpin mmChuangxinchanpin=new Chuangxinchanpin();
            mmChuangxinchanpin.setId(UUID.randomUUID().toString());
            mmChuangxinchanpin.setXiangmuid(ppXiangmuId);
            mmChuangxinchanpin.setYaopinmingcheng(ppYaopinmingcheng);
            mmChuangxinchanpin.setGuige(ppGuige);
            mmChuangxinchanpin.setDanwei(ppDanwei);
            mmChuangxinchanpin.setJiage(ppJiage);
            mmChuangxinchanpin.setShengchanchangjia(ppShengchanchangjia);
            mmChuangxinchanpin.setZhixingshijian(ppZhixingshijian);
            mmChuangxinchanpin.setYuanbenbu(ppYuanbenbu);
            mmChuangxinchanpin.setQinhuai(ppQinhuai);
            mmChuangxinchanpin.setOrdernumber(Integer.parseInt(ppXuhao));
            mmChuangxinchanpin.setCreatetime(new Date());


            return ddChuangxinchanpinService.add(mmChuangxinchanpin)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_chuangxinchanpin: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //6.新药申请
    @RequestMapping("import_xinyaoshenqing")
    public JsonResult<?> importXinyaoshenqingvalue(
            @RequestParam("xiangmuid") String ppXiangmuId,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("guigebaozhuang") String ppGuigebaozhuang,
            @RequestParam("shenqingkeshi") String ppShenqingkeshi,
            @RequestParam("zhuankezubie") String ppZhuankezubie,
            @RequestParam("shifoujiyao") String ppShifoujiyao,
            @RequestParam("shifouchuangxin") String ppShifouchuangxin,
            @RequestParam("shifouzhinan") String ppShifouzhinan,
            @RequestParam("shifoulinchuang") String ppShifoulinchuang,
            @RequestParam("lincainianfen") String ppLincainianfen,
            @RequestParam("xuhao") String ppXuhao

    ){
        try {
            Xinyaoshenqing mmXinyaoshenqing=new Xinyaoshenqing();
            mmXinyaoshenqing.setId(UUID.randomUUID().toString());
            mmXinyaoshenqing.setXiangmuid(ppXiangmuId);
            mmXinyaoshenqing.setYaopinmingcheng(ppYaopinmingcheng);
            mmXinyaoshenqing.setGuigebaozhuang(ppGuigebaozhuang);
            mmXinyaoshenqing.setShenqingkeshi(ppShenqingkeshi);
            mmXinyaoshenqing.setZhuankezubie(ppZhuankezubie);
            mmXinyaoshenqing.setShifoujiyao(ppShifoujiyao);
            mmXinyaoshenqing.setShifouchuangxin(ppShifouchuangxin);
            mmXinyaoshenqing.setShifouzhinan(ppShifouzhinan);
            mmXinyaoshenqing.setShifoulinchuang(ppShifoulinchuang);
            mmXinyaoshenqing.setLincainianfen(ppLincainianfen);
            mmXinyaoshenqing.setOrdernumber(Integer.parseInt(ppXuhao));
            mmXinyaoshenqing.setCreatetime(new Date());

            return ddXinyaoshenqingService.add(mmXinyaoshenqing)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_xinyaoshenqing: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //7.约采
    @RequestMapping("import_yuecaikangjun")
    public JsonResult<?> importYuecaikangjunvalue(
            @RequestParam("xiangmuid") String ppXiangmuId,
            @RequestParam("yaopinmingcheng") String ppYaopinmingcheng,
            @RequestParam("yibao") String ppYibao,
            @RequestParam("yuanbenbuyao") String ppYuanbenbuyao,
            @RequestParam("qinhuaiyao") String ppQinhuaiyao,
            @RequestParam("nanjingchuangxin") String ppNanjingchuangxin,
           /* @RequestParam("zhuankefenzu") String ppZhuankefenzu,
            @RequestParam("shenqingkeshi") String ppShenqingkeshi,*/
            @RequestParam("beizhu4") String ppBeizhu4,
            @RequestParam("beizhu") String ppBeizhu,
            @RequestParam("beizhu2") String ppBeizhu2,
            @RequestParam("beizhu3") String ppBeizhu3,
            @RequestParam("xuhao") String ppXuhao

    ){
        try {
            Yuecaikangjunyaowu mmYuecaikangjunyaowu=new Yuecaikangjunyaowu();
            mmYuecaikangjunyaowu.setId(UUID.randomUUID().toString());
            mmYuecaikangjunyaowu.setXiangmuid(ppXiangmuId);
            mmYuecaikangjunyaowu.setYaopinmingcheng(ppYaopinmingcheng);
            mmYuecaikangjunyaowu.setYibao(ppYibao);
            mmYuecaikangjunyaowu.setYuanbenbuyao(ppYuanbenbuyao);
            mmYuecaikangjunyaowu.setQinhuaiyao(ppQinhuaiyao);
            mmYuecaikangjunyaowu.setNanjingchuangxin(ppNanjingchuangxin);
           /* mmYuecaikangjunyaowu.setZhuankefenzu(ppZhuankefenzu);
            mmYuecaikangjunyaowu.setShenqingkeshi(ppShenqingkeshi);*/
            mmYuecaikangjunyaowu.setBeizhu4(ppBeizhu4);
            mmYuecaikangjunyaowu.setBeizhu(ppBeizhu);
            mmYuecaikangjunyaowu.setBeizhu2(ppBeizhu2);
            mmYuecaikangjunyaowu.setBeizhu3(ppBeizhu3);
            mmYuecaikangjunyaowu.setOrdernumber(Integer.parseInt(ppXuhao));
            mmYuecaikangjunyaowu.setCreatetime(new Date());


            return ddYuecaiangjunyaowuService.add(mmYuecaikangjunyaowu)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_kangjun: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    //8.生产企业
    @RequestMapping("import_shengchanqiye")
    public JsonResult<?> importShengchanqiyevalue(
            @RequestParam("xianmguid") String ppxianmguid,
            @RequestParam("pinzhongbianhao") String pppinzhongbianhao,
            @RequestParam("yaopinpinming") String ppyaopinpinming,
            @RequestParam("jixing") String ppjixing,
            @RequestParam("pinguixuhao") String pppinguixuhao,
            @RequestParam("yaopinmingcheng") String ppyaopinmingcheng,
            @RequestParam("guige") String ppguige,
            @RequestParam("danwei") String ppdanwei,
            @RequestParam("yibaoleibie") String ppyibaoleibie,
            @RequestParam("jianchatongbao") String ppjianchatongbao,
            @RequestParam("weiguawang") String ppweiguawang,
            @RequestParam("wupeisongqiye") String ppwupeisongqiye,
            @RequestParam("yuanguotan") String ppyuanguotan,
            @RequestParam("yizhixingpingjia") String ppyizhixingpingjia,
            @RequestParam("baomipeifang") String ppbaomipeifang,
            @RequestParam("baohupinzhong") String ppbaohupinzhong,
            @RequestParam("jiage") String ppjiage,
            @RequestParam("yuanyan") String ppyuanyan,
            @RequestParam("jiyao") String ppjiyao,
            @RequestParam("jicai") String ppjicai,
            @RequestParam("yuanbenbu") String ppyuanbenbu,
            @RequestParam("qinhuai") String ppqinhuai,
            @RequestParam("chanpinbianma") String ppchanpinbianma,
            @RequestParam("shengchanqiye") String ppshengchanqiye,
            @RequestParam("beizhu") String ppbeizhu,
            @RequestParam("ordernumber") String ppordernumber,
            @RequestParam("beizhu1") String ppbeizhu1,
            @RequestParam("beizhu2") String ppbeizhu2,
            @RequestParam("beizhu3") String ppbeizhu3,
            @RequestParam("beizhu4") String ppbeizhu4,
            @RequestParam("beizhu5") String ppbeizhu5,
            @RequestParam("beizhu6") String ppbeizhu6


    ){
        try {
            Shengchanqiyelinxuan mmShengchan=new Shengchanqiyelinxuan();
            mmShengchan.setId(UUID.randomUUID().toString());
            mmShengchan.setXiangmuid(ppxianmguid);
            mmShengchan.setPinzhongbianhao(pppinzhongbianhao);
            mmShengchan.setYaopinpinming(ppyaopinpinming);
            mmShengchan.setJixing(ppjixing);
            mmShengchan.setPinguixuhao(pppinguixuhao);
            mmShengchan.setYaopinmingcheng(ppyaopinmingcheng);
            mmShengchan.setGuige(ppguige);
            mmShengchan.setDanwei(ppdanwei);
            mmShengchan.setYibaoleibie(ppyibaoleibie);
            mmShengchan.setJianchatongbao(ppjianchatongbao);
            mmShengchan.setWeiguawang(ppweiguawang);
            mmShengchan.setWupeisongqiye(ppwupeisongqiye);
            mmShengchan.setYuanguotan(ppyuanguotan);
            mmShengchan.setYizhixingpingjia(ppyizhixingpingjia);
            mmShengchan.setBaomipeifang(ppbaomipeifang);
            mmShengchan.setBaohupinzhong(ppbaohupinzhong);
            mmShengchan.setJiage(ppjiage);
            mmShengchan.setYuanyan(ppyuanyan);
            mmShengchan.setJicai(ppjicai);
            mmShengchan.setJiyao(ppjiyao);
            mmShengchan.setYuanbenbu(ppyuanbenbu);
            mmShengchan.setQinhuai(ppqinhuai);
            mmShengchan.setChanpinbianma(ppchanpinbianma);
            mmShengchan.setShengchanqiye(ppshengchanqiye);
            mmShengchan.setBeizhu(ppbeizhu);
            mmShengchan.setBeizhu1(ppbeizhu1);
            mmShengchan.setBeizhu2(ppbeizhu2);
            mmShengchan.setBeizhu3(ppbeizhu3);
            mmShengchan.setBeizhu4(ppbeizhu4);
            mmShengchan.setBeizhu5(ppbeizhu5);
            mmShengchan.setBeizhu6(ppbeizhu6);
            mmShengchan.setCreatetime(new Date());
            mmShengchan.setSuijilunci("1");
            mmShengchan.setOrdernumber(Integer.parseInt(ppordernumber));


            return ddShengchanqiyelinxuanService.add(mmShengchan)==true? JsonResult.getSuccessResult("导入成功")
                    : JsonResult.getErrorResult("导入失败");

        }catch (Exception e) {
            e.printStackTrace();
            logger.error("import_xiangmu -> import_shengchanqiye: "+e.getMessage());
            return JsonResult.getErrorResult("导入失败！");
        }
    }

    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {

            cell.setCellType(CellType.STRING);
            // 判断当前Cell的Type
            switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        // data格式是带时分秒的：2013-7-10 0:00:00
                        // cellvalue = cell.getDateCellValue().toLocaleString();
                        // data格式是不带带时分秒的：2013-7-10
                        Date date = cell.getDateCellValue();
                        cellvalue = date;
                    } else {// 如果是纯数字
                        cell.setCellType(1);
                        // 取得当前Cell的数值
                        //cellvalue = String.valueOf(cell.getNumericCellValue());
                        cellvalue=cell.getStringCellValue();
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:// 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

}
